package 数据库.day3;

import 数据库.DBUtil;


import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

public class StudentDao {
    public int add(Student s) {
        Connection connection = null;
        PreparedStatement st = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "insert into s_student(sname,sex,telephone,qq,wechat,gradeid,enterdate,fromcity,birthday,address,pass,idcardpic) values(?,?,?,?,?,?,?,?,?,?,?,? )";
            System.out.println(sql);
            st = connection.prepareStatement(sql);
            st.setString(1, s.getSname());
            st.setString(2, s.getSex());
            st.setString(3, s.getTelephone());
            st.setString(4, s.getQq());
            st.setString(5, s.getWechat());
            st.setString(6, s.getGradeid());
            st.setObject(7,s.getEnterdate());
            st.setString(8, s.getFromcity());
            st.setObject(9,s.getBirthday());
            st.setString(10, s.getAddress());
            st.setString(11, s.getPass());
            st.setString(12, s.getIdcardpic());
            return st.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(null, st, connection);
        }
        return 0;
    }

    public int update(Student s) {
        Connection connection = null;
        PreparedStatement st = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "UPDATE  s_student  SET  sname=?,sex=?,telephone=?,qq=?,wechat=?,gradeid=?,enterdate=?,fromcity=?,birthday=?,address=?,pass=?,idcardpic=null  where  stuno=?";
            System.out.println(sql);
            st = connection.prepareStatement(sql);
            st.setString(1, s.getSname());
            st.setString(2, s.getSex());
            st.setString(3, s.getTelephone());
            st.setString(4, s.getQq());
            st.setString(5, s.getWechat());
            st.setString(6, s.getGradeid());
            st.setObject(7,s.getEnterdate());
            st.setString(8, s.getFromcity());
            st.setObject(9,s.getBirthday());
            st.setString(10, s.getAddress());
            st.setString(11, s.getPass());
            st.setLong(12, s.getStuno());
            return st.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(null, st, connection);
        }
        return 0;
    }

    public int del(long stuno) {
        Connection connection = null;
        PreparedStatement st = null;
        try {
            connection = DBUtil.getConnection();
            Scanner sc = new Scanner(System.in);

            String sql = "delete   from s_student  where  stuno=?";
            System.out.println(sql);
            st = connection.prepareStatement(sql);
            st.setLong(1, stuno);
            return st.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(null, st, connection);
        }
        return 0;
    }

    //主键找对象
    public Student get(long stuno) {
        Connection connection = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        Student s=null;
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from  s_student  where  stuno=?";
            System.out.println(sql);
            st = connection.prepareStatement(sql);
            st.setLong(1, stuno);
            rs = st.executeQuery();
            if (rs.next()){
                s=new Student(rs.getLong(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getDate(8),rs.getString(9),rs.getDate(10),rs.getString(11),rs.getString(12),rs.getString(13));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(null, st, connection);
        }
        return s;
    }

    //查询全部对象
    public List<Student> findALL() {
        String sql = "select * from s_student   order by stuno asc";
        List<Student> datas = new ArrayList<>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = DBUtil.getConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                datas.add(new Student(rs.getLong(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getDate(8),rs.getString(9),rs.getDate(10),rs.getString(11),rs.getString(12),rs.getString(13)));
            }
        } catch (SQLException e) {
            // DBUtil.handlerEx(e);
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, ps, con);
        }
        return datas;
    }

    public List<Student> search(int pageNo,int pageSize) {
        int i=(pageNo-1)*pageSize;
        String sql = "select * from s_student  order by stuno asc limit ?,?";
        List<Student> datas = new ArrayList<>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = DBUtil.getConnection();
            ps = con.prepareStatement(sql);
            ps.setInt(1, i);
            ps.setInt(2, pageSize);
            rs = ps.executeQuery();
            while (rs.next()) {
                datas.add(new Student(rs.getLong(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getDate(8),rs.getString(9),rs.getDate(10),rs.getString(11),rs.getString(12),rs.getString(13)));
            }
        } catch (SQLException e) {
           // DBUtil.handlerEx(e);
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, ps, con);
        }
        return datas;
    }
    public static int executeUpdate(String sql,Object...args){
        Connection connection = null;
        PreparedStatement st = null;
        try {
            connection = DBUtil.getConnection();
            System.out.println(sql);
            st = connection.prepareStatement(sql);
            for (int i=0;i<args.length;i++)
                st.setObject(i+1,args[i]);
            return st.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(null, st, connection);
        }
        return 0;
    }

}

